package cn.openea.perpartner.meily.web.mapper;

import cn.openea.perpartner.meily.web.entity.GroupBuyingSub;
import cn.openea.perpartner.meily.web.entity.dto.GoodsSalesDataDTO;
import cn.openea.perpartner.meily.web.entity.vo.OrderSalesDataVo;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * (GroupBuyingSub)表数据库访问层
 *
 * @author rains
 * @since 2023-02-07 17:15
 */
@Repository
public interface GroupBuyingSubMapper extends BaseMapper<GroupBuyingSub> {

    /**
     * 获取商品销量
     *
     * @return
     */
    @Select("select ifnull(sum(buy_quantity),0) from meily_group_buying_sub where 1=1  ${ew.customSqlSegment}")
    Long getTotalBuyQuantity(@Param("ew") QueryWrapper<GroupBuyingSub> wrapper);

    /**
     * 获取自动收货前48h(大于24小时)的购买记录
     * 说明：精确到秒，按发货时间算
     *
     * @return
     */
    @Select("select * from meily_group_buying_sub where order_status = '已发货' HAVING timestampdiff(SECOND, now(), date_add(DATE_FORMAT(delivery_time, '%Y-%m-%d %H:%m:%s'), interval 10 day)) BETWEEN 86400 and 172800 ORDER BY delivery_time")
    List<GroupBuyingSub> getNeedToSendMsgList();

    /**
     * 获取已到期需要平台自动收货前的购买记录
     * 说明：精确到秒，按发货时间算
     *
     * @return
     */
    @Select("select * from meily_group_buying_sub where order_status = '已发货' HAVING timestampdiff(SECOND, now(), date_add(DATE_FORMAT(delivery_time, '%Y-%m-%d %H:%m:%s'), interval (delayed_receipt_days + 10) day)) <= 0 ORDER BY delivery_time")
    List<GroupBuyingSub> getBecomeDueAutoReceiptList();

    /**
     * 获取当前月已结束的拼团的所有参与记录
     *
     * @return
     */
    @Select("select * from meily_group_buying_sub where main_docunid in (select id from meily_group_buying where DATE_FORMAT(finish_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') and status = '2') order by join_time desc")
    List<GroupBuyingSub> getCurrentMonthGroupFinishJoinList();

    /**
     * 获取上个月已结束的拼团的所有参与记录
     *
     * @return
     */
    @Select("select * from meily_group_buying_sub where main_docunid in (select id from meily_group_buying where DATE_FORMAT(finish_time, '%Y%m') = DATE_FORMAT(date_sub(CURDATE(), interval 1 month), '%Y%m') and status = '2') order by join_time desc")
    List<GroupBuyingSub> getLastMonthGroupFinishJoinList();

    /**
     * 订单状态统计查询
     */
    @Select("SELECT\n" +
            "    SUM(buy_quantity) as ALLSALESQUANTITY,\n" +
            "    CONCAT(IFNULL(SUM(CASE WHEN order_status = '已付款' THEN 1 ELSE 0 END), 0)) AS PAYMENT,\n" +
            "    CONCAT(IFNULL(SUM(CASE WHEN order_status = '已发货' THEN 1 ELSE 0 END), 0)) AS SHIPPED,\n" +
            "    CONCAT(IFNULL(SUM(CASE WHEN order_status = '已完成' THEN 1 ELSE 0 END), 0)) AS COMPLETED,\n" +
            "    CONCAT(IFNULL(SUM(CASE WHEN order_status = '已退款' THEN 1 ELSE 0 END), 0)) AS REFUNDED\n" +
            "FROM meily_group_buying_sub ${ew.customSqlSegment}")
    Map<String, Integer> orderStatusCountQuery(@Param("ew") LambdaQueryWrapper<GroupBuyingSub> buildQueryWrapper);

    /**
     * 销售数据统计列表
     * @param salesDataVo 查询条件
     */
    List<GoodsSalesDataDTO> goodSSalesDataList(OrderSalesDataVo salesDataVo);

    /**
     * 全部销售数据统计
     * @param salesDataVo 查询条件
     */
    Map<String,Object> statisticsAllGoodsSalesData(OrderSalesDataVo salesDataVo);


    /**
     * 获取用户当前周期已购买的数量
     *
     * @return
     */
    @Select("select ifnull(sum(buy_quantity),0) from meily_group_buying_sub where 1=1  ${ew.customSqlSegment}")
    long getQuantityByCurrentCycle(@Param("ew") QueryWrapper<GroupBuyingSub> wrapper);
}
